import psycopg2
from psycopg2 import Error
import threading
import time
import random

# Database connection parameters
DB_PARAMS = {
    "user": "postgres",
    "password": "ffxshlsy666",  # Replace with your actual password
    "host": "localhost",
    "port": "5432",
    "database": "transaction_lab"
}

def get_connection():
    """Create a new database connection."""
    return psycopg2.connect(**DB_PARAMS)

def create_account(name, initial_balance=0.00):
    """Create a new bank account."""
    try:
        connection = get_connection()
        cursor = connection.cursor()
        connection.autocommit = False

        insert_query = """
        INSERT INTO accounts (name, balance) VALUES (%s, %s)
        RETURNING account_id;
        """
        cursor.execute(insert_query, (name, initial_balance))
        account_id = cursor.fetchone()[0]

        if initial_balance > 0:
            record_transaction = """
            INSERT INTO transactions (to_account, amount) VALUES (%s, %s);
            """
            cursor.execute(record_transaction, (account_id, initial_balance))

        connection.commit()
        print(f"Account created: ID {account_id}, Name: {name}, Balance: {initial_balance}")
        return account_id

    except (Exception, Error) as error:
        if connection:
            connection.rollback()
        print(f"Error creating account: {error}")
        return None
    finally:
        if connection:
            cursor.close()
            connection.close()

def deposit(account_id, amount):
    """Deposit money into an account."""
    try:
        connection = get_connection()
        cursor = connection.cursor()
        connection.autocommit = False

        update_query = "UPDATE accounts SET balance = balance + %s WHERE account_id = %s;"
        cursor.execute(update_query, (amount, account_id))

        record_transaction = """
        INSERT INTO transactions (to_account, amount) VALUES (%s, %s);
        """
        cursor.execute(record_transaction, (account_id, amount))

        connection.commit()
        print(f"Deposited {amount} into account ID {account_id}.")
        return True

    except (Exception, Error) as error:
        if connection:
            connection.rollback()
        print(f"Error during deposit: {error}")
        return False
    finally:
        if connection:
            cursor.close()
            connection.close()

def withdraw(account_id, amount):
    """Withdraw money from an account."""
    try:
        connection = get_connection()
        cursor = connection.cursor()
        connection.autocommit = False

        check_balance = "SELECT balance FROM accounts WHERE account_id = %s;"
        cursor.execute(check_balance, (account_id,))
        current_balance = cursor.fetchone()
        if not current_balance:
            print(f"Account ID {account_id} not found.")
            connection.rollback()
            return False

        if current_balance[0] < amount:
            print(f"Insufficient funds in account ID {account_id}! Balance: {current_balance[0]}, Requested: {amount}")
            connection.rollback()
            return False

        update_query = "UPDATE accounts SET balance = balance - %s WHERE account_id = %s;"
        cursor.execute(update_query, (amount, account_id))

        record_transaction = """
        INSERT INTO transactions (from_account, amount) VALUES (%s, %s);
        """
        cursor.execute(record_transaction, (account_id, amount))

        connection.commit()
        print(f"Withdrew {amount} from account ID {account_id}.")
        return True

    except (Exception, Error) as error:
        if connection:
            connection.rollback()
        print(f"Error during withdrawal: {error}")
        return False
    finally:
        if connection:
            cursor.close()
            connection.close()

def transfer_money(from_account_id, to_account_id, amount):
    """Transfer money between accounts."""
    try:
        connection = get_connection()
        cursor = connection.cursor()
        connection.autocommit = False

        check_balance = "SELECT balance FROM accounts WHERE account_id = %s;"
        cursor.execute(check_balance, (from_account_id,))
        current_balance = cursor.fetchone()
        if not current_balance:
            print(f"Source account ID {from_account_id} not found.")
            connection.rollback()
            return False

        if current_balance[0] < amount:
            print(f"Insufficient funds! Balance: {current_balance[0]}, Requested: {amount}")
            connection.rollback()
            return False

        cursor.execute(check_balance, (to_account_id,))
        if not cursor.fetchone():
            print(f"Destination account ID {to_account_id} not found.")
            connection.rollback()
            return False

        update_source = "UPDATE accounts SET balance = balance - %s WHERE account_id = %s;"
        cursor.execute(update_source, (amount, from_account_id))

        update_destination = "UPDATE accounts SET balance = balance + %s WHERE account_id = %s;"
        cursor.execute(update_destination, (amount, to_account_id))

        record_transaction = """
        INSERT INTO transactions (from_account, to_account, amount) VALUES (%s, %s, %s);
        """
        cursor.execute(record_transaction, (from_account_id, to_account_id, amount))

        connection.commit()
        print(f"Transferred {amount} from ID {from_account_id} to ID {to_account_id}.")
        return True

    except (Exception, Error) as error:
        if connection:
            connection.rollback()
        print(f"Error during transfer: {error}")
        return False
    finally:
        if connection:
            cursor.close()
            connection.close()

def view_transaction_history(account_id):
    """View transaction history for an account."""
    try:
        connection = get_connection()
        cursor = connection.cursor()

        query = """
        SELECT transaction_id, from_account, to_account, amount, transaction_date
        FROM transactions
        WHERE from_account = %s OR to_account = %s
        ORDER BY transaction_date DESC;
        """
        cursor.execute(query, (account_id, account_id))
        transactions = cursor.fetchall()

        if not transactions:
            print(f"No transactions found for account ID {account_id}.")
            return []

        print(f"Transaction history for account ID {account_id}:")
        for tx in transactions:
            print(f"ID: {tx[0]}, From: {tx[1] or 'N/A'}, To: {tx[2] or 'N/A'}, Amount: {tx[3]}, Date: {tx[4]}")
        return transactions

    except (Exception, Error) as error:
        print(f"Error viewing transaction history: {error}")
        return []
    finally:
        if connection:
            cursor.close()
            connection.close()

def simulate_user(user_id, account_pairs, amounts):
    """Simulate a user performing random transfers."""
    for _ in range(5):
        from_acc, to_acc = random.choice(account_pairs)
        amount = random.choice(amounts)
        print(f"User {user_id} attempting transfer: {amount} from {from_acc} to {to_acc}")
        success = transfer_money(from_acc, to_acc, amount)
        if success:
            print(f"User {user_id} transfer succeeded.")
        else:
            print(f"User {user_id} transfer failed.")
        time.sleep(random.uniform(0.1, 0.5))

def simulate_concurrent_transactions(num_users=5, account_ids=[1, 2, 3]):
    """Simulate multiple users performing concurrent transactions."""
    account_pairs = [(i, j) for i in account_ids for j in account_ids if i != j]
    amounts = [50.00, 100.00, 150.00, 200.00]

    threads = []
    for user_id in range(1, num_users + 1):
        thread = threading.Thread(target=simulate_user, args=(user_id, account_pairs, amounts))
        threads.append(thread)
        thread.start()

    for thread in threads:
        thread.join()
    print("Concurrent transactions simulation completed.")

if __name__ == "__main__":
    # Ensure tables and initial data are created first
    # Run create_tables.py and insert_data.py separately before this

    # Example usage for Exercise 1
    # create_account("David", 300.00)
    # deposit(1, 100.00)
    # withdraw(1, 50.00)
    # transfer_money(1, 2, 200.00)
    # view_transaction_history(1)

    # Exercise 2: Simulate concurrent transactions
    simulate_concurrent_transactions()